/*
 * ProductDAO.java
 *
 * $Id: ProductDAO.java,v 1.4 2009-03-02 15:31:23 mario Exp $
 */
package org.ceteca.explica.server.warehouse.dao;

import java.util.ArrayList;
import java.util.List;

import org.ceteca.explica.core.ExplicaException;
import org.ceteca.explica.core.InterfaceErrors;
import org.ceteca.explica.core.util.OperationResult;
import org.ceteca.explica.core.util.ResultMessage;
import org.ceteca.explica.core.warehouse.InterfaceWarehouseErrors;
import org.ceteca.explica.core.warehouse.ProductOT;
import org.ceteca.explica.server.ServerContext;
import org.ceteca.explica.server.util.db.CriteriaBean;
import org.ceteca.explica.server.util.db.HibernateUtil;
import org.ceteca.explica.server.util.db.SQLGenerator;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;

/**
 * This class implements the product data access object. It's 
 * a part of the data access layer and gives access to the database data. 
 * It knows the database structure and implementation. 
 * <br/>
 * This class is implemented to access a PostgreSQL database implementation.
 * <br/>
 * Responsibilities:
 * <li>
 * 	<ul> Provide statements for inserting new products into the database. </ul>
 *  <ul> Provide statements for updating products into the database. </ul>
 *  <ul> Provide statements for deleting products from the database. </ul>
 *  <ul> Provide the database queries needed for products operations.  </ul>
 * </li>
 */

public class ProductDAO {
	
	/**
	 * Default constructor of the ProductDAO class.
	 */
	public ProductDAO() {
	}
	
	/**
	 * Query the database to get all the available products. 
	 * @throws Exception, in case any error take place during
	 * the query operation.
	 * @return ArrayList, list of all the products.
	 */
	public List getAllProducts() throws Exception {
		List result = new ArrayList();
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();
		
        try {
        	// Load the objects
        	result = session.createQuery(
            "from ProductOT p ORDER BY p.reference")
            .list();

            System.out.println( "Consulta realizada! " +
            		result == null || result.size() <= 0?(" No se ha encontrado ningún producto!"):
            		("Se han encontrado: " + result.size() + " productos"));
        }
        catch (HibernateException ex) {
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().
        		newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCT_NOT_FOUND, ex);
        	throw new ExplicaException(msg);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query (by primary key) the database to get a product data. 
	 * @param idProduct long, Internal id of the product.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 * @return ProductOT, product if it exists, null in any other case.
	 */
	public ProductOT getProduct(long idProduct) throws ExplicaException {
		ProductOT result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Load the object by the primary key (idResource)
        	result = (ProductOT)session.get(ProductOT.class, new Long(idProduct));

            System.out.println( "Consulta realizada! " +
            		result == null?(" No se ha encontrado ningún producto correspondiente!"):
            		("Se ha encontrado el producto: " + result.getDescription()));
        }
        catch (HibernateException ex) {
        	ArrayList lParams = new ArrayList();
        	lParams.add("" + idProduct);
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCT_NOT_FOUND, ex, lParams);
        	throw new ExplicaException(msg);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query the database to get a product data. 
	 * @param reference String, product reference.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 * @return ProductOT, product if it exists, null in any other case.
	 */
	public ProductOT getProduct(String reference) throws ExplicaException {
		ProductOT result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Build the query statement
        	String sql = "FROM ProductOT r WHERE r.reference=?";
        	Query q = session.createQuery(sql);
        	q.setString(0, reference);

        	// Run the query statement
        	result = (ProductOT)q.uniqueResult();

            System.out.println( "Consulta realizada! " +
            		result == null?(" No se ha encontrado ningún producto correspondiente!"):
            		("Se ha encontrado el producto: " + result.getDescription()));
        }
        catch (HibernateException ex) {
        	ArrayList lParams = new ArrayList();
        	lParams.add("" + reference);
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCT_NOT_FOUND, ex, lParams);
        	throw new ExplicaException(msg);
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Query the database to get a list of products that math
	 * the query criteria specified. 
	 * @param lCriteria ArrayList<CriteriaBean>, list of query criteria.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 * @return ArrayList<ProductOT>, list of products matching the 
	 * searching criteria.
	 */
	public ArrayList<ProductOT> getProducts(ArrayList<CriteriaBean> lCriteria) throws ExplicaException {
		ArrayList<ProductOT> result = null;
		// Open a new Hibernate session to query the Database
		Session session = HibernateUtil.getSessionFactory().openSession();

        try {
        	// Build the query statement
        	
/*        	Criteria crit = session.createCriteria(ProductOT.class);
        	crit.add(
            		Restrictions.or(
            			Restrictions.and(
            					Restrictions.and(
            							Restrictions.lt("idProduct", new Long(10)),
            							Restrictions.or(
       											Restrictions.lt("price", new BigDecimal(30.00)),
       											Restrictions.and(
            											Restrictions.gt("reference", new String("4000")),
            											Restrictions.eq("idProduct", new Long(4))
            									)
            							)
            					),
            					Restrictions.lt("price", new BigDecimal(100.00))	
            			),
            			Restrictions.eq("idProduct", new Long(1))
            		)
            );
*/
        	
        	SQLGenerator sqlGen = new SQLGenerator();
        	
/*        	lCriteria = new ArrayList<CriteriaBean>();
        	lCriteria.add(new CriteriaBean("idProduct", new Long(10), InterfaceDB.SQL_OPERATOR_LOWER, null, null, null));
        	lCriteria.add(new CriteriaBean("price", new BigDecimal(30.00), InterfaceDB.SQL_OPERATOR_LOWER, InterfaceDB.LOGIC_OPERATOR_AND, "(", null));
        	lCriteria.add(new CriteriaBean("reference", new String("4000"), InterfaceDB.SQL_OPERATOR_GREATER, InterfaceDB.LOGIC_OPERATOR_OR, null, null));
        	lCriteria.add(new CriteriaBean("idProduct", new Long(4), InterfaceDB.SQL_OPERATOR_EQUAL, InterfaceDB.LOGIC_OPERATOR_AND, null, ")"));
        	lCriteria.add(new CriteriaBean("price", new BigDecimal(100.00), InterfaceDB.SQL_OPERATOR_LOWER, InterfaceDB.LOGIC_OPERATOR_AND, null, null));
        	lCriteria.add(new CriteriaBean("idProduct", new Long(1), InterfaceDB.SQL_OPERATOR_EQUAL, InterfaceDB.LOGIC_OPERATOR_OR, null, null));
*/        	
        	
        	Criteria crit = sqlGen.getSQLCriteria(session, ProductOT.class, lCriteria);

        	crit.addOrder(Order.asc("reference"));
        	// Run the query statement
        	result = (ArrayList)crit.list();

            System.out.println( "Consulta realizada! " +
            		result == null?(" No se ha encontrado ningún producto correspondiente!"):
            		("Se han encontrado productos: " + result.size()));
        }
        catch (HibernateException ex) {
/*        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_PRODUCT_NOT_FOUND, ex, null);
        	throw new ExplicaException(msg);
*/
        	ex.printStackTrace();
        }
        
        // Close the Hibernate session
        session.close();	
        
        // Return the result of the query
        return result;
	}
	
	/**
	 * Updates the database removing the selected product.
	 * @param idProduct Long, internal id of the selected product. 
	 * @return OperationResult, result of the operation and list of messages
	 * generated through the operation.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 */
	public OperationResult delProduct(Long idProduct) throws ExplicaException {
		OperationResult result = new OperationResult();
		
		if (idProduct == null) {
			result.addResultMessage(OperationResult.RESULT_ERROR, 
					ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_DELETE_PRODUCT_NULL));
		}
		else {
			// Open a new Hibernate session to query the Database
			Session session = HibernateUtil.getSessionFactory().openSession();
	
			// Build the list of parameters for the localized operation result message.
			ArrayList lParams = new ArrayList();
	    	lParams.add(idProduct);
			
	    	
	    	// Open a new transaction
	        Transaction tx = session.beginTransaction();
	
	        // Delete the product from the database
	        try {
	        	ProductOT product = (ProductOT)session.load(ProductOT.class, idProduct);
	        	System.out.println("Product (" + product.getIdProduct() + ") found: " + product.getDescription());
	        	
	        	session.delete(product);
	        	tx.commit();
	        	
	        	System.out.println("Product (" + product.getIdProduct() + ") deleted: " + product.getDescription());
	        	result.addResultMessage(OperationResult.RESULT_OK, 
	        			ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.MSG_WAREHOUSE_DELETE_PRODUCT_OK, lParams));
	        }
	        catch (HibernateException ex) {
	        	ex.printStackTrace();
	        	tx.rollback();
	        	
	        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_DELETE_PRODUCT, ex, lParams);
	        	throw new ExplicaException(msg);
	        }
	        
	        // Close the Hibernate session
	        session.close();
	        
	        System.out.println("Session closed!");
		}
        
        return result;
	}
	
	public OperationResult delProducts(ArrayList<Long> lSelProducts) throws ExplicaException {
		OperationResult result = new OperationResult();
		
        try {
        	// Build the query statement
        	for (int i=0; lSelProducts != null && i<lSelProducts.size(); i++) {
        		// Delete the product
        		result.add(this.delProduct(lSelProducts.get(i)));
        	} // for each product
        }
        catch (HibernateException ex) {
        	ex.printStackTrace();
        	
        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_DELETE_PRODUCT, ex, null);
        	throw new ExplicaException(msg);
        }
        
        return result;
	}
	
	/**
	 * Updates the database with the data of the edited entity registry. 
	 * It's responsible of saving new entity registries (add a new registry) 
	 * as well as of updating the contents of existing entity registries.
	 * @param product ProductOT, product instance with the new data. 
	 * @return OperationResult, result of the operation and list of messages
	 * generated through the operation.
	 * @throws ExplicaException, in case any error take place during
	 * the query operation.
	 */
	public OperationResult saveProduct(ProductOT product) throws ExplicaException {
		OperationResult result = new OperationResult();

		if (product == null) {
			result.addResultMessage(OperationResult.RESULT_ERROR, 
					ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_SAVE_PRODUCT_NULL));
		}
		else {
	
			// Build the list of parameters for the localized operation result message.
			ArrayList lParams = new ArrayList();
			lParams.add(product.getReference());
		
			// Open a new Hibernate session to query the Database
			Session session = HibernateUtil.getSessionFactory().openSession();
	
	    	// Open a new transaction
	        Transaction tx = session.beginTransaction();
	
	        // Update the product in the database
	        try {
	        	session.saveOrUpdate(product);
	        	tx.commit();
	        	
	        	lParams.add(product.getIdProduct());
	        	
	        	System.out.println("Product (" + product.getIdProduct() + ") saved: " + product.getDescription());
	        	result.addResultMessage(OperationResult.RESULT_OK, 
	        			ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.MSG_WAREHOUSE_SAVE_PRODUCT_OK, lParams));
	        }
	        catch (HibernateException ex) {
	        	ex.printStackTrace();
	        	tx.rollback();
	        	
	        	ResultMessage msg = ServerContext.getInstance().getMsgCatalog().newResultMessage(InterfaceWarehouseErrors.ERR_WAREHOUSE_SAVE_PRODUCT, ex, lParams);
	        	throw new ExplicaException(msg);
	        }
	        
	        // Close the Hibernate session
	        session.close();
	        
	        System.out.println("Session closed!");
		} // else product!=null
        
        return result;
	}
	
	/**
	 * Returns tag Id assigned to CVS source file.
	 */
	public static String getRevision() {
		return "$Id: ProductDAO.java,v 1.4 2009-03-02 15:31:23 mario Exp $";
	}
}
